package com.eascs.web.o2o.web.service;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.web.dao.BidmDao;
import com.eascs.web.o2o.web.form.ChannelBusStatisForm;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;

@Service
public class ChannelBusStatisService {

	
	@Autowired
	BidmDao bidmDao;
	
	
	public PageResult<Object[]> getChannelBusStatisByPage(ChannelBusStatisForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(bidmDao.getSql("sql.ChannelBusStatisService.getChannelBusStatisByPage")); 
		User user = UserHolder.getUser();
		//自己看自己
		querySql.append(" AND t.partner_user_no = ?");
		sqlPageQuery.addParam(user.getNo());
		if(!StringUtil.isEmpty(entity.getBillCode())){
			querySql.append(" AND t.bill_code = ?");
			sqlPageQuery.addParam(entity.getBillCode());
		}
		if(!StringUtil.isEmpty(entity.getLoanType())){
			querySql.append(" and t.loan_type_code in ( ");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(entity.getLoanType().contains(",")){
				loanTypeA = entity.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+entity.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(entity.getPartnerType())){
			querySql.append(" and t.partner_type_code in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(entity.getPartnerType().contains(",")){
				loanTypeA = entity.getPartnerType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+entity.getPartnerType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(entity.getLoanerName())){
			querySql.append(" AND t.loaner_name LIKE ?");
			sqlPageQuery.addLikeParam(entity.getLoanerName());
		}
		if(!StringUtil.isEmpty(entity.getPartnerName())){
			querySql.append(" AND t.partner_name = ?");
			sqlPageQuery.addParam(entity.getPartnerName());
		}
		if(!StringUtil.isEmpty(entity.getLoanDate())){
			querySql.append(" AND t.loan_date >= ?");
			sqlPageQuery.addParam(entity.getLoanDate());
		}
		if(!StringUtil.isEmpty(entity.getLoanDateEnd())){
			querySql.append(" AND t.loan_date <= ?");
			sqlPageQuery.addParam(entity.getLoanDateEnd());
		}
		querySql.append("  ORDER BY t.loan_date DESC ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	//总部看全部
	public PageResult<Object[]> getChannelBusStatisByPage1(ChannelBusStatisForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(bidmDao.getSql("sql.ChannelBusStatisService.getChannelBusStatisByPage1")); 
		if(!StringUtil.isEmpty(entity.getBillCode())){
			querySql.append(" AND t.bill_code = ?");
			sqlPageQuery.addParam(entity.getBillCode());
		}
		if(!StringUtil.isEmpty(entity.getLoanType())){
			querySql.append(" and t.loan_type_code in ( ");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(entity.getLoanType().contains(",")){
				loanTypeA = entity.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+entity.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(entity.getPartnerType())){
			querySql.append(" and t.partner_type_code in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(entity.getPartnerType().contains(",")){
				loanTypeA = entity.getPartnerType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+entity.getPartnerType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(entity.getLoanerName())){
			querySql.append(" AND t.loaner_name LIKE ?");
			sqlPageQuery.addLikeParam(entity.getLoanerName());
		}
		if(!StringUtil.isEmpty(entity.getPartnerName())){
			querySql.append(" AND t.partner_name = ?");
			sqlPageQuery.addParam(entity.getPartnerName());
		}
		if(!StringUtil.isEmpty(entity.getLoanDate())){
			querySql.append(" AND t.loan_date >= ?");
			sqlPageQuery.addParam(entity.getLoanDate());
		}
		if(!StringUtil.isEmpty(entity.getLoanDateEnd())){
			querySql.append(" AND t.loan_date <= ?");
			sqlPageQuery.addParam(entity.getLoanDateEnd());
		}
		querySql.append("  ORDER BY t.loan_date DESC ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	
	
	
	
	public PageResult<Object[]>  getBrandChannelBusStatisByPage(ChannelBusStatisForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(bidmDao.getSql("sql.ChannelBusStatisService.getBrandChannelBusStatisByPage")); 
		
		if(!StringUtil.isEmpty(entity.getBrandName())){
			querySql.append(" AND t.brand_name LIKE ?");
			sqlPageQuery.addLikeParam(entity.getBrandName());
		}
		if(!StringUtil.isEmpty(entity.getLoanStatus())){
			querySql.append(" AND t.loan_status = ?");
			sqlPageQuery.addParam(entity.getLoanStatus());
		}
		if(!StringUtil.isEmpty(entity.getProvince())){
			querySql.append(" AND t.acq_province = ?");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if(!StringUtil.isEmpty(entity.getLoanDateS())){
			querySql.append(" AND t.loan_date >= ?");
			sqlPageQuery.addParam(entity.getLoanDateS());
		}
		if(!StringUtil.isEmpty(entity.getLoanDateE())){
			querySql.append(" AND t.loan_date <= ?");
			sqlPageQuery.addParam(entity.getLoanDateE());
		}
		if(!StringUtil.isEmpty(entity.getExpireDateS())){
			querySql.append(" AND t.end_date >= ?");
			sqlPageQuery.addParam(entity.getExpireDateS());
		}
		if(!StringUtil.isEmpty(entity.getExpireDateE())){
			querySql.append(" AND t.end_date <= ?");
			sqlPageQuery.addParam(entity.getExpireDateE());
		}
		querySql.append(" order by t.brand_code,t.acq_code  ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return bidmDao.findDataWithPageByDw(sqlPageQuery);
	}
	
	
	
	
	public PageResult<Object[]>  getRestViewLoanDataByPage(ChannelBusStatisForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(bidmDao.getSql("sql.ChannelBusStatisService.getRestViewLoanDataByPage")); 
		
		if(!StringUtil.isEmpty(entity.getProvince())){
			querySql.append(" AND t.province_name = ?");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if(!StringUtil.isEmpty(entity.getLoanDateS())){
			querySql.append(" AND t.loan_date >= ?");
			sqlPageQuery.addParam(entity.getLoanDateS());
		}
		if(!StringUtil.isEmpty(entity.getLoanDateE())){
			querySql.append(" AND t.loan_date <= ?");
			sqlPageQuery.addParam(entity.getLoanDateE());
		}
		querySql.append("    order by t.loan_amount desc  ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	public List<Object[]> findPubDdAll() {
		List<Object[]> dataSouceList = new ArrayList<>();
		List<Object> listParams = new ArrayList<Object>();
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT DISTINCT m.item_code,m.item_name FROM ods_o2oservice_pub_dditem m  ");
		querySql.append("  ");
		querySql.append(" WHERE 1=1 AND m.is_lock = 'N' ");
		querySql.append(" AND m.dd_no = '11321' ");	// #贷款类型OTO_LOAN_TYPE
//		querySql.append(" AND m.item_code IN('SG','YC','SC') ");// #只筛选出蔬果贷、烟草贷、商超贷
//		querySql.append(" AND m.item_code NOT IN('EA','FQ') ");	// #排除:接力贷、分期贷
		querySql.append(" ORDER BY m.item_order ASC ");
		dataSouceList = bidmDao.findObjectsListByBiods(querySql.toString(), listParams.toArray());
		return dataSouceList;
	}
	
	
	
	
	
	
	
	
	
	
	
	public PageResult<Object[]> getChannelListForOwnByPage(ChannelBusStatisForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(bidmDao.getSql("sql.ChannelBusStatisService.getChannelListForOwnByPage")); 
		User user = UserHolder.getUser();
		//自己看自己
		querySql.append(" AND t.partner_user_no = ?");
		sqlPageQuery.addParam(user.getNo());
		if(!StringUtil.isEmpty(entity.getPartnerType())){
			querySql.append(" and t.partner_type_code in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(entity.getPartnerType().contains(",")){
				loanTypeA = entity.getPartnerType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+entity.getPartnerType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(entity.getProvince())){
			querySql.append(" AND t.partner_province = ?");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if(!StringUtil.isEmpty(entity.getPartnerName())){
			querySql.append(" AND t.partner_name = ?");
			sqlPageQuery.addParam(entity.getPartnerName());
		}
		if(!StringUtil.isEmpty(entity.getPartnerStartDate())){
			querySql.append(" AND t.partner_start_date >= ?");
			sqlPageQuery.addParam(entity.getPartnerStartDate());
		}
		if(!StringUtil.isEmpty(entity.getPartnerEndDate())){
			querySql.append(" AND t.partner_start_date <= ?");
			sqlPageQuery.addParam(entity.getPartnerEndDate());
		}
		querySql.append("  ORDER BY t.partner_loan_count DESC ");
		sqlPageQuery.setQuerySql(querySql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] {  5, 6});
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	
	public PageResult<Object[]> getChannelListForCommonByPage(ChannelBusStatisForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(bidmDao.getSql("sql.ChannelBusStatisService.getChannelListForCommonByPage")); 
		if(!StringUtil.isEmpty(entity.getPartnerType())){
			querySql.append(" and t.partner_type_code in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(entity.getPartnerType().contains(",")){
				loanTypeA = entity.getPartnerType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+entity.getPartnerType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(entity.getProvince())){
			querySql.append(" AND t.partner_province = ?");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if(!StringUtil.isEmpty(entity.getPartnerName())){
			querySql.append(" AND t.partner_name = ?");
			sqlPageQuery.addParam(entity.getPartnerName());
		}
		if(!StringUtil.isEmpty(entity.getPartnerStartDate())){
			querySql.append(" AND t.partner_start_date >= ?");
			sqlPageQuery.addParam(entity.getPartnerStartDate());
		}
		if(!StringUtil.isEmpty(entity.getPartnerEndDate())){
			querySql.append(" AND t.partner_start_date <= ?");
			sqlPageQuery.addParam(entity.getPartnerEndDate());
		}
		querySql.append("  ORDER BY t.partner_loan_count DESC ");
		sqlPageQuery.setQuerySql(querySql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] {  5,6 });
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	public List<Object[]> getChannelObject(String code){
		String sql=bidmDao.getSql("sql.ChannelBusStatisService.getChannelObject");
		return bidmDao.findObjectsListByBiods(sql,new Object[]{code});
	}
	
	
	public PageResult<Object[]> getChannelforBill(ChannelBusStatisForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(bidmDao.getSql("sql.ChannelBusStatisService.getChannelforBill")); 
		sqlPageQuery.addParam(entity.getPartnerCode());
		sqlPageQuery.setQuerySql(querySql.toString());
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
}
